#include "datastore.h"

DataStore::DataStore()
{
    _db = QSqlDatabase::addDatabase( "QSQLITE" );
    QString dbfile( QCoreApplication::applicationDirPath() + "/" + Constant::dbname );
    if( !QFile( dbfile ).exists() ) createDb( dbfile );
    _db.setDatabaseName( dbfile );
    if( _db.open() )
    {
        loadPaises();
        loadUbicaciones(); // must be after paises
        loadSitios(); // must be after ubicaciones
        loadViajes();
        loadBuceadores();
        loadBuddies(); // must be after buceadores
        QMapIterator<quint32,Buceador*> i( _buceadores );
        quint32 idbuceador = 0;
        Buceador* buceador = NULL;
        while( i.hasNext() )
        {
            i.next();
            idbuceador = i.key();
            buceador = i.value();
            loadInmersiones( buceador, idbuceador ); // must be the last
            loadEstadisticas( buceador->estadisticas(), idbuceador );
        }
    }
}

void DataStore::createDb( const QString& dbfile )
{
    qDebug() << "copying empty database to" << dbfile;
    QFile::copy( ":/divelog.db.empty", dbfile );
}

void DataStore::deleteInmersion( Inmersion* inmersion )
{
    QMutableHashIterator<quint32,Inmersion*> i(_inmersiones);
    Inmersion* current = NULL;
    while( i.hasNext() )
    {
        i.next();
        current = i.value();
        if( current->buceador()->usuario() == inmersion->buceador()->usuario() && current->inicio() == inmersion->inicio() )
        {
            // id = i.key();
            // TODO: DELETE FROM Inmersiones WHERE Id = :id
            // TODO: DELETE FROM Muestras WHERE IdInmersion = :id
            i.remove();
            inmersion->buceador()->removeInmersion( current );
            delete current;
            break;
        }
    }
}

void DataStore::loadPaises()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"     // query.value(0).toUInt();
                        "Nombre " // query.value(1).toString();
                   "FROM Paises "
                   "ORDER BY Nombre" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString nombre = query.value(1).toString();
        _paises.insert( id, new Pais( nombre ) );
        //qDebug() << "+++ PAIS: " << nombre;
    }
}

void DataStore::loadUbicaciones()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"        // query.value(0).toUInt();
                        "Nombre,"    // query.value(1).toString();
                        "IdPais,"    // query.value(2).toUInt();
                        "Latitude,"  // query.value(3).toReal();
                        "Longitude " // query.value(4).toReal();
                   "FROM Ubicaciones "
                   "ORDER BY Nombre" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString nombre = query.value(1).toString();
        quint32 idpais = query.value(2).toUInt();
        qreal latitude = query.value(3).toReal();
        qreal longitude = query.value(4).toReal();
        Pais* pais = _paises.value( idpais );
        if( !pais )
        {
            qWarning() << "Could not find Pais with id " << idpais << ": will not add Ubicacion with id " << id;
            continue;
        }
        _ubicaciones.insert( id, new Ubicacion( nombre, pais, latitude, longitude ) );
        //qDebug() << "+++ UBICACION: " << nombre << " (" << pais->nombre() << ")";
    }
}

void DataStore::loadSitios()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"          // query.value(0).toUInt();
                        "Nombre,"      // query.value(1).toString();
                        "IdUbicacion " // query.value(2).toUInt();
                   "FROM Sitios "
                   "ORDER BY Nombre" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString nombre = query.value(1).toString();
        quint32 idubicacion = query.value(2).toUInt();
        Ubicacion* ubicacion = _ubicaciones.value( idubicacion );
        if( !ubicacion )
        {
            qWarning() << "Could not find Ubicacion with id " << idubicacion << ": will not add Sitio with id " << id;
            continue;
        }
        _sitios.insert( id, new Sitio( nombre, ubicacion ) );
        //qDebug() << "+++ SITIO: " << nombre << " (" << ubicacion->nombre() << ")";
    }
}

void DataStore::loadViajes()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"      // query.value(0).toUInt();
                        "Destino " // query.value(1).toString();
                   "FROM Viajes "
                   "ORDER BY Destino" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString destino = query.value(1).toString();
        _viajes.insert( id, new Viaje( destino ) );
        //qDebug() << "+++ VIAJE: " << destino;
    }
}

void DataStore::loadBuceadores()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"      // query.value(0).toUInt();
                        "Usuario " // query.value(1).toString();
                   "FROM Buceadores "
                   "ORDER BY Usuario" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    Buceador* buceador = NULL;
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString usuario = query.value(1).toString();
        QString mapurl = getMapUrl( id );
        buceador = new Buceador( usuario, mapurl );
        _buceadores.insert( id, buceador );
        //qDebug() << "+++ BUCEADOR: " << usuario;
    }
}

void DataStore::loadEstadisticas( Estadisticas* estadisticas, quint32 idbuceador )
{
    QSqlQuery query;
    query.setForwardOnly( true );

    query.prepare( "SELECT COUNT(*) FROM Inmersiones WHERE IdBuceador=:idbuceador" );
    query.bindValue( ":idbuceador", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setNumeroInmersiones( query.value(0).toUInt() );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT SUM(Duracion) FROM Inmersiones WHERE IdBuceador=:idbuceador" );
    query.bindValue( ":idbuceador", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setTiempoTotalFondo( query.value(0).toUInt() );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT ROUND(MIN(JULIANDAY('NOW')-JULIANDAY(Inicio))) FROM Inmersiones WHERE IdBuceador=:idbuceador" );
    query.bindValue( ":idbuceador", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setDiasDesdeUtima( query.value(0).toUInt() );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT Id FROM Inmersiones WHERE IdBuceador=:idbuceador1 AND IntervaloSuperficie=(SELECT MIN(IntervaloSuperficie) FROM Inmersiones WHERE IdBuceador=:idbuceador2)" );
    query.bindValue( ":idbuceador1", idbuceador );
    query.bindValue( ":idbuceador2", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setIntervaloMasCorto( _inmersiones.value( query.value(0).toUInt() ) );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT Id FROM Inmersiones WHERE IdBuceador=:idbuceador1 AND ProfundidadMaxima=(SELECT MAX(ProfundidadMaxima) FROM Inmersiones WHERE IdBuceador=:idbuceador2)" );
    query.bindValue( ":idbuceador1", idbuceador );
    query.bindValue( ":idbuceador2", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setMasProfunda( _inmersiones.value( query.value(0).toUInt() ) );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT Id FROM Inmersiones WHERE IdBuceador=:idbuceador1 AND ProfundidadMaxima=(SELECT MIN(ProfundidadMaxima) FROM Inmersiones WHERE IdBuceador=:idbuceador2)" );
    query.bindValue( ":idbuceador1", idbuceador );
    query.bindValue( ":idbuceador2", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setMenosProfunda( _inmersiones.value( query.value(0).toUInt() ) );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT Id FROM Inmersiones WHERE IdBuceador=:idbuceador1 AND TemperaturaFondo=(SELECT MAX(TemperaturaFondo) FROM Inmersiones WHERE IdBuceador=:idbuceador2)" );
    query.bindValue( ":idbuceador1", idbuceador );
    query.bindValue( ":idbuceador2", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setMasCalurosa( _inmersiones.value( query.value(0).toUInt() ) );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();

    query.prepare( "SELECT Id FROM Inmersiones WHERE IdBuceador=:idbuceador1 AND TemperaturaFondo=(SELECT MIN(TemperaturaFondo) FROM Inmersiones WHERE IdBuceador=:idbuceador2)" );
    query.bindValue( ":idbuceador1", idbuceador );
    query.bindValue( ":idbuceador2", idbuceador );
    if( query.exec() )
    {
        if( query.next() )
            estadisticas->setMasFria( _inmersiones.value( query.value(0).toUInt() ) );
        else qWarning() << "Error retrieving result of" << query.lastQuery() << ":" << query.lastError().text();
    }
    else qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
}

void DataStore::loadBuddies()
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"         // query.value(0).toUInt();
                        "Nombre,"     // query.value(1).toString();
                        "IdBuceador " // query.value(2).toUInt();
                   "FROM Buddies "
                   "ORDER BY Nombre" );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    Buddy* buddy = NULL;
    while( query.next() )
    {
        quint32 id = query.value(0).toUInt();
        QString nombre = query.value(1).toString();
        Buceador* buceador = NULL;
        if( !query.isNull(2) )
        {
            buceador = _buceadores.value( query.value(2).toUInt(), NULL );
            if( !buceador ) qWarning() << "Could not find Buceador with id " << query.value(2).toUInt();
        }
        buddy = new Buddy( nombre, buceador );
        _buddies.insert( id, buddy );
        //qDebug() << "+++ BUDDY: " << nombre;
    }
}

QString DataStore::getMapUrl( quint32 id ) const
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT DISTINCT "
                        "u.Latitude || ',' || u.Longitude AS Coordenates "
                    "FROM "
                        "Buceadores b "
                        "LEFT JOIN Inmersiones i ON i.IdBuceador = b.Id "
                        "LEFT JOIN Sitios s ON i.IdSitio = s.Id "
                        "LEFT JOIN Ubicaciones u ON s.IdUbicacion = u.Id "
                   "WHERE b.Id=:id "
                   "ORDER BY Coordenates" );
    query.bindValue( ":id", id );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    QStringList coordenates;
    while( query.next() ) coordenates << query.value(0).toString();
    return( Constant::mapurlstart + coordenates.join( "%7C" ) );
}

void DataStore::loadInmersiones( Buceador* buceador, quint32 idbuceador )
{
    if( idbuceador == 0 )
    {
        idbuceador = findBuceador( buceador );
        if( idbuceador == 0 )
        {
            qWarning() << "Cannot load inmersiones for buceador" << buceador->usuario() << "- usuario not found";
            return;
        }
    }
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                        "Id,"                   // query.value(0).toUInt();
                        "Inicio,"               // query.value(1).toDateTime();
                        "Duracion,"             // query.value(2).toUInt();
                        "ProfundidadMaxima,"    // query.value(3).toReal();
                        "ProfundidadMedia,"     // query.value(4).toReal();
                        "IdSitio,"              // query.value(5).toUInt();
                        "IntervaloSuperficie,"  // query.value(6).toUInt();
                        "TemperaturaAire,"      // query.value(7).toUInt();
                        "TemperaturaSuperficie,"// query.value(8).toUInt();
                        "TemperaturaFondo,"     // query.value(9).toUInt();
                        "VolumenBotella,"       // query.value(10).toUInt();
                        "PresionInicial,"       // query.value(11).toUInt();
                        "PresionFinal,"         // query.value(12).toUInt();
                        "Lastre,"               // query.value(13).toReal();
                        "PorcentajeO2,"         // query.value(14).toUInt();
                        "IdBuddy,"              // query.value(15).toUInt();
                        "IdViaje "              // query.value(16).toUInt();
                   "FROM Inmersiones "
                   "WHERE IdBuceador = :idbuceador "
                   "ORDER BY Inicio ASC" );
    query.bindValue( ":idbuceador", idbuceador );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    Inmersion* inmersion = NULL;
    quint32 numero = 1;
    while( query.next() )
    {
        Sitio* sitio = _sitios.value( query.value(5).toUInt() );
        if( !sitio )
        {
            qWarning() << "Could not find Sitio with id " << query.value(5).toUInt() << ": will not add Inmersion with id " << query.value(0).toUInt();
            continue;
        }
        quint32 id = query.value(0).toUInt();
        QDateTime inicio = query.value(1).toDateTime();
        quint32 duracion = query.value(2).toUInt();
        qreal profundidadmaxima = query.value(3).toReal();
        qreal profundidadmedia = query.value(4).toReal();
        qnuint intervalosuperficie = ( query.isNull(6) ? qnuint() : qnuint( query.value(6).toUInt() ) );
        qnuint temperaturaaire = ( query.isNull(7) ? qnuint() : qnuint( query.value(7).toUInt() ) );
        qnuint temperaturasuperficie = ( query.isNull(8) ? qnuint() : qnuint( query.value(8).toUInt() ) );
        qnuint temperaturafondo = ( query.isNull(9) ? qnuint() : qnuint( query.value(9).toUInt() ) );
        qnuint volumenbotella = ( query.isNull(10) ? qnuint() : qnuint( query.value(10).toUInt() ) );
        qnuint presioninicial = ( query.isNull(11) ? qnuint() : qnuint( query.value(11).toUInt() ) );
        qnuint presionfinal = ( query.isNull(12) ? qnuint() : qnuint( query.value(12).toUInt() ) );
        qreal lastre = query.value(13).toReal();
        quint32 porcentajeo2 = query.value(14).toUInt();
        Buddy* buddy = _buddies.value( query.value(15).toUInt() );
        Viaje* viaje = NULL;
        if( !query.isNull(16) ) viaje = _viajes.value( query.value(16).toUInt() );
        inmersion = new Inmersion( buceador,
                                   numero,
                                   inicio,
                                   duracion,
                                   profundidadmaxima,
                                   profundidadmedia,
                                   sitio,
                                   intervalosuperficie,
                                   temperaturaaire,
                                   temperaturasuperficie,
                                   temperaturafondo,
                                   volumenbotella,
                                   presioninicial,
                                   presionfinal,
                                   lastre,
                                   porcentajeo2,
                                   buddy,
                                   viaje );
        loadMuestras( inmersion, id );
        buceador->addInmersion( inmersion );
        _inmersiones.insert( id, inmersion );
        numero++;
        //qDebug() << "+++ INMERSION " << buceador->usuario() << ": " << inicio.toString() << " (" << sitio->nombre() << ")";
    }
}

void DataStore::loadMuestras( Inmersion* inmersion, quint32 idinmersion )
{
    QSqlQuery query;
    query.setForwardOnly( true );
    query.prepare( "SELECT "
                       "Tiempo,"          // query.value(0).UInt()
                       "Profundidad,"     // query.value(1).toReal()
                       "Presion,"         // query.value(2).UInt()
                       "Temperatura,"     // query.value(3).UInt()
                       "Marcador,"        // query.value(4).UInt()
                       "TasaCAS,"         // query.value(5).toReal()
                       "PresionCilindro " // query.value(6).UInt()
                    "FROM Muestras "
                    "WHERE IdInmersion = :idinmersion "
                    "ORDER BY Tiempo ASC" );
    query.bindValue( ":idinmersion", idinmersion );
    if( !query.exec() ) qWarning() << "Error executing query" << query.lastQuery() << ":" << query.lastError().text();
    Muestra* muestra = NULL;
    quint32 maxtiempo = 0;
    while( query.next() )
    {
        quint32 tiempo = query.value(0).toUInt();
        qreal profundidad = query.value(1).toReal();
        quint32 presion = query.value(2).toUInt();
        quint32 temperatura = query.value(3).toUInt();
        Muestra::Marcador marcador = static_cast<Muestra::Marcador>( query.value(4).toUInt() );
        qreal tasacas = query.value(5).toReal();
        quint32 presioncilindro = query.value(6).toUInt();
        muestra = new Muestra( tiempo, profundidad, presion, temperatura, marcador, tasacas, presioncilindro );
        if( tiempo > maxtiempo ) maxtiempo = tiempo;
        inmersion->addMuestra( tiempo, muestra );
        //qDebug() << "+++ MUESTRA " << inmersion->sitio()->nombre() << ": " << muestra->tiempo() << "s";
    }
    if( maxtiempo > 0 )
        inmersion->setMaxTiempo( maxtiempo );
    else inmersion->setMaxTiempo( inmersion->duracion() );
}

Buceador* DataStore::buceador( const QString& usuario ) const
{
    QMapIterator<quint32,Buceador*> i( _buceadores );
    Buceador* buceador = NULL;
    while( i.hasNext() )
    {
        i.next();
        buceador = i.value();
        if( buceador->usuario() == usuario ) return buceador;
    }
    return NULL;
}

quint32 DataStore::insertInmersion( const QDateTime& inicio,
                                    quint32 duracion,
                                    quint32 idsitio,
                                    quint32 idbuceador,
                                    quint32 intervalosuperficie,
                                    qreal profundidadmaxima,
                                    qreal profundidadmedia,
                                    const qnuint& temperaturaaire,
                                    const qnuint& temperaturasuperficie,
                                    const qnuint& temperaturafondo,
                                    const qnuint& volumenbotella,
                                    const qnuint& presioninicial,
                                    const qnuint& presionfinal,
                                    qreal lastre,
                                    quint32 porcentajeo2,
                                    quint32 idbuddy,
                                    quint32 idviaje )
{
    QSqlQuery query;
    query.prepare( "INSERT INTO Inmersiones("
                        "Inicio," // NOT NULL
                        "Duracion," // NOT NULL
                        "IdSitio," // NOT NULL
                        "IdBuceador," // NOT NULL
                        "IntervaloSuperficie,"
                        "ProfundidadMaxima," // NOT NULL
                        "ProfundidadMedia," // NOT NULL
                        "TemperaturaAire,"
                        "TemperaturaSuperficie,"
                        "TemperaturaFondo,"
                        "VolumenBotella,"
                        "PresionInicial,"
                        "PresionFinal,"
                        "Lastre," // NOT NULL
                        "PorcentajeO2," // NOT NULL
                        "IdBuddy,"
                        "IdViaje "
                   ") VALUES("
                        ":inicio,"
                        ":duracion,"
                        ":idsitio,"
                        ":idbuceador,"
                        ":intervalosuperficie,"
                        ":profundidadmaxima,"
                        ":profundidadmedia,"
                        ":temperaturaaire,"
                        ":temperaturasuperficie,"
                        ":temperaturafondo,"
                        ":volumenbotella,"
                        ":presioninicial,"
                        ":presionfinal,"
                        ":lastre,"
                        ":porcentajeo2,"
                        ":idbuddy,"
                        ":idviaje "
                   ")" );
    query.bindValue( ":inicio", inicio.toString( "yyyy-MM-dd hh:mm:ss" ) );
    query.bindValue( ":duracion", duracion );
    query.bindValue( ":idsitio", idsitio );
    query.bindValue( ":idbuceador", idbuceador );
    query.bindValue( ":intervalosuperficie", ( (intervalosuperficie!=0) ? intervalosuperficie : QVariant( QVariant::UInt ) ) );
    query.bindValue( ":profundidadmaxima", profundidadmaxima );
    query.bindValue( ":profundidadmedia", profundidadmedia );
    query.bindValue( ":temperaturaaire", ( temperaturaaire.isNull() ? QVariant( QVariant::UInt ) : temperaturaaire.value() ) );
    query.bindValue( ":temperaturasuperficie", ( temperaturasuperficie.isNull() ? QVariant( QVariant::UInt ) : temperaturasuperficie.value() ) );
    query.bindValue( ":temperaturafondo", ( temperaturafondo.isNull() ? QVariant( QVariant::UInt ) : temperaturafondo.value() ) );
    query.bindValue( ":volumenbotella", ( volumenbotella.isNull() ? QVariant( QVariant::UInt ) : volumenbotella.value() ) );
    query.bindValue( ":presioninicial", ( presioninicial.isNull() ? QVariant( QVariant::UInt ) : presioninicial.value() ) );
    query.bindValue( ":presionfinal", ( presionfinal.isNull() ? QVariant( QVariant::UInt ) : presionfinal.value() ) );
    query.bindValue( ":lastre", lastre );
    query.bindValue( ":porcentajeo2", porcentajeo2 );
    query.bindValue( ":idbuddy", ( (idbuddy!=0) ? idbuddy : QVariant( QVariant::UInt ) ) );
    query.bindValue( ":idviaje", ( (idviaje!=0) ? idviaje : QVariant( QVariant::UInt ) ) );
    if( !query.exec() )
    {
        qWarning() << "Error executing query:" << query.lastQuery() << " - " << query.lastError().text();
        return 0;
    }
    QVariant id = query.lastInsertId();
    if( !id.isValid() )
    {
        qWarning() << "Error getting id of inserted inmersion";
        return 0;
    }
    qDebug() << "inserted inmersion" << inicio.toString( "yyyy-MM-dd hh:mm:ss" ) << _sitios.value( idsitio )->nombre();
    return id.toUInt();
}

quint32 DataStore::findSitio( const QString& sitionombre, const QString ubicacionnombre )
{
    Sitio* sitio = NULL;
    QHashIterator<quint32,Sitio*> i( _sitios );
    while( i.hasNext() )
    {
        i.next();
        sitio = i.value();
        if( sitio->nombre() == sitionombre && sitio->ubicacion()->nombre() == ubicacionnombre ) return i.key();
    }
    return 0;
}

quint32 DataStore::findBuceador( Buceador* buceador )
{
    Buceador* current = NULL;
    QMapIterator<quint32,Buceador*> i( _buceadores );
    while( i.hasNext() )
    {
        i.next();
        current = i.value();
        if( current->usuario() == buceador->usuario() ) return i.key();
    }
    return 0;
}

quint32 DataStore::findBuddy( const QString& buddynombre )
{
    Buddy* buddy = NULL;
    QHashIterator<quint32,Buddy*> i( _buddies );
    while( i.hasNext() )
    {
        i.next();
        buddy = i.value();
        if( buddy->nombre() == buddynombre ) return i.key();
    }
    return 0;
}

void DataStore::importXml( Buceador* buceador, const QString& filepath, bool createnew )
{
    qDebug() << "_________________________" << endl << filepath;
    QFile importfile( filepath );
    if( !importfile.open( QIODevice::ReadOnly ) )
    {
        qWarning() << "Error opening" << filepath;
        return;
    }
    QXmlStreamReader reader;
    reader.setDevice( &importfile );
    QXmlStreamReader::TokenType tokentype = QXmlStreamReader::NoToken;
    QDate importdate;
    QTime importtime;
    quint32 idbuceador = 0;
    quint32 idsitio = 0;
    quint32 idbuddy = 0;
    quint32 duracion = 0;
    quint32 intervalosuperficie = 0;
    quint32 porcentajeo2 = 0;
    qreal profundidadmaxima = 0;
    qreal profundidadmedia = 0;
    qreal lastre = 0;
    qnuint temperaturaaire;
    qnuint temperaturasuperficie;
    qnuint temperaturafondo;
    qnuint volumenbotella;
    qnuint presioninicial;
    qnuint presionfinal;
    QString sitiotext;
    QString ubicaciontext;
    QString elementtext;
    quint32 elementint = 0;
    qreal elementdouble = 0;
    quint32 samplecnt = 0;
    //quint32 sampleinterval = 0;
    bool conversionok = true;
    bool inmuestra = false;
    bool generalsacrateread = false;
    Inmersion* inmersion = NULL;
    Muestra* muestra = NULL;
    QList<Muestra*> muestras;
    while( !reader.atEnd() )
    {
        tokentype = reader.readNext();
        if( tokentype == QXmlStreamReader::StartElement )
        {
            if( reader.name() == "SAMPLECNT" )
            {
                elementtext = reader.readElementText();
                samplecnt = elementtext.toUInt( &conversionok );
                if( !conversionok ) qWarning() << "SAMPLECNT is invalid:" << elementtext;
            }
            /*else if( reader.name() == "SAMPLEINTERVAL" )
            {
                elementtext = reader.readElementText();
                sampleinterval = elementtext.toUInt( &conversionok );
                if( !conversionok ) qWarning() << "SAMPLEINTERVAL is invalid:" << elementtext;
            }*/
            else if( reader.name() == "DATE" )
            {
                elementtext = reader.readElementText();
                importdate = QDate::fromString( elementtext, "dd.MM.yyyy" );
                if( !importdate.isValid() ) qWarning() << "DATE is invalid:" << elementtext;
            }
            else if( reader.name() == "TIME" )
            {
                elementtext = reader.readElementText();
                importtime = QTime::fromString( elementtext, "HH:mm:ss" );
                if( !importtime.isValid() ) qWarning() << "TIME is invalid:" << elementtext;
            }
            else if( reader.name() == "SITE" )
            {
                sitiotext = reader.readElementText();
                if( !ubicaciontext.isEmpty() )
                {
                    idsitio = findSitio( sitiotext, ubicaciontext );
                    if( idsitio == 0 ) qWarning() << "SITIO not found:" << sitiotext << "(" << ubicaciontext << ")";
                }
            }
            else if( reader.name() == "PARTNER" )
            {
                elementtext = reader.readElementText();
                idbuddy = findBuddy( elementtext );
                if( idbuddy == 0 ) qWarning() << "PARTNER not found:" << elementtext;
            }
            else if( reader.name() == "LOCATION" )
            {
                ubicaciontext = reader.readElementText();
                if( !sitiotext.isEmpty() )
                {
                    idsitio = findSitio( sitiotext, ubicaciontext );
                    if( idsitio == 0 ) qWarning() << "SITIO not found:" << sitiotext << "(" << ubicaciontext << ")";
                }
            }
            else if( reader.name() == "DIVETIMESEC" )
            {
                elementtext = reader.readElementText();
                duracion = elementtext.toUInt( &conversionok );
                if( !conversionok ) qWarning() << "DIVETIMESEC is invalid:" << elementtext;
            }
            else if( reader.name() == "SURFACETIME" )
            {
                elementtext = reader.readElementText();
                intervalosuperficie = elementtext.toUInt( &conversionok );
                if( !conversionok ) qWarning() << "SURFACETIME is invalid:" << elementtext;
            }
            else if( reader.name() == "MAXDEPTH" )
            {
                elementtext = reader.readElementText();
                profundidadmaxima = QLocale::c().toDouble( elementtext.replace( ',', '.' ), &conversionok );
                if( !conversionok ) qWarning() << "MAXDEPTH is invalid:" << elementtext;
            }
            else if( reader.name() == "MEANDEPTH" )
            {
                elementtext = reader.readElementText();
                profundidadmedia = QLocale::c().toDouble( elementtext.replace( ',', '.' ), &conversionok );
                if( !conversionok ) qWarning() << "MEANDEPTH is invalid:" << elementtext;
            }
            else if( reader.name() == "AIRTEMP" )
            {
                elementtext = reader.readElementText();
                temperaturaaire.setValue( elementtext.toUInt( &conversionok ) );
                if( !conversionok )
                {
                    temperaturaaire.setNull( true );
                    qWarning() << "AIRTEMP is invalid:" << elementtext;
                }
            }
            else if( reader.name() == "WATERTEMPMAXDEPTH" )
            {
                elementtext = reader.readElementText();
                temperaturafondo.setValue( elementtext.toUInt( &conversionok ) );
                if( !conversionok )
                {
                    temperaturafondo.setNull( true );
                    qWarning() << "WATERTEMPMAXDEPTH is invalid:" << elementtext;
                }
            }
            else if( reader.name() == "WATERTEMPATEND" )
            {
                elementtext = reader.readElementText();
                temperaturasuperficie.setValue( elementtext.toUInt( &conversionok ) );
                if( !conversionok )
                {
                    temperaturasuperficie.setNull( true );
                    qWarning() << "WATERTEMPATEND is invalid:" << elementtext;
                }
            }
            else if( reader.name() == "CYLINDERSIZE" )
            {
                elementtext = reader.readElementText();
                volumenbotella.setValue( elementtext.toUInt( &conversionok ) );
                if( !conversionok )
                {
                    volumenbotella.setNull( true );
                    qWarning() << "CYLINDERSIZE is invalid:" << elementtext;
                }
            }
            else if( reader.name() == "CYLINDERSTARTPRESSURE" )
            {
                elementtext = reader.readElementText();
                if( elementtext != "0" ) // don't do anything if the value is 0
                {
                    presioninicial.setValue( elementtext.toUInt( &conversionok ) );
                    if( !conversionok )
                    {
                        presioninicial.setNull( true );
                        qWarning() << "CYLINDERSTARTPRESSURE is invalid:" << elementtext;
                    }
                }
            }
            else if( reader.name() == "CYLINDERENDPRESSURE" )
            {
                elementtext = reader.readElementText();
                if( elementtext != "0" ) // don't do anything if the value is 0
                {
                    presionfinal.setValue( elementtext.toUInt( &conversionok ) );
                    if( !conversionok )
                    {
                        presionfinal.setNull( true );
                        qWarning() << "CYLINDERENDPRESSURE is invalid:" << elementtext;
                    }
                }
            }
            else if( reader.name() == "O2PCT" )
            {
                elementtext = reader.readElementText();
                porcentajeo2 = elementtext.toUInt( &conversionok );
                if( !conversionok ) qWarning() << "O2PCT is invalid:" << elementtext;
            }
            else if( reader.name() == "WEIGTH" )
            {
                elementtext = reader.readElementText();
                lastre = QLocale::c().toDouble( elementtext.replace( ',', '.' ), &conversionok );
                if( !conversionok ) qWarning() << "WEIGTH is invalid:" << elementtext;
            }
            else if( reader.name() == "SAMPLE" )
            {
                if( inmuestra )
                    qWarning() << "SAMPLE start while still in other SAMPLE";
                else
                {
                    muestra = new Muestra();
                    inmuestra = true;
                }
            }
            else if( reader.name() == "SAMPLETIME" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementint = elementtext.toUInt( &conversionok );
                    if( conversionok )
                        muestra->setTiempo( elementint );
                    else qWarning() << "SAMPLETIME is invalid:" << elementtext;
                }
                else qWarning() << "SAMPLETIME outside of Muestra";
            }
            else if( reader.name() == "DEPTH" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementdouble = QLocale::c().toDouble( elementtext.replace( ',', '.' ), &conversionok );
                    if( conversionok )
                        muestra->setProfundidad( elementdouble );
                    else qWarning() << "DEPTH is invalid:" << elementtext;
                }
                else qWarning() << "DEPTH outside of Muestra";
            }
            else if( reader.name() == "PRESSURE" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementint = elementtext.toUInt( &conversionok );
                    if( conversionok )
                        muestra->setPresion( elementint );
                    else qWarning() << "PRESSURE is invalid:" << elementtext;
                }
                else qWarning() << "PRESSURE outside of Muestra";
            }
            else if( reader.name() == "TEMPERATURE" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementint = elementtext.toUInt( &conversionok );
                    if( conversionok )
                        muestra->setTemperatura( elementint );
                    else qWarning() << "TEMPERATURE is invalid:" << elementtext;
                }
                else qWarning() << "TEMPERATURE outside of Muestra";
            }
            /*else if( reader.name() == "BOOKMARK" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    if( elementtext == "Aviso de ascenso" ) muestra->setMarcador( Muestra::AvisoAscenso );
                    else if( elementtext == "Aviso de rgbm" ) muestra->setMarcador( Muestra::AvisoRgbm );
                    else if( elementtext == "Error de techo de parada de seg. obligatoria" ) muestra->setMarcador( Muestra::ErrorTechoPso );
                    else if( elementtext == "Parada de seguridad obligatoria" ) muestra->setMarcador( Muestra::ParadaSeguridadObligatoria );
                    else if( elementtext == "Superficie" ) muestra->setMarcador( Muestra::Superficie );
                    else if( !elementtext.isEmpty() ) qWarning() << "BOOKMARK unknown:" << elementtext;
                }
                else qWarning() << "BOOKMARK outside of Muestra";
            }*/
            else if( reader.name() == "BOOKMARKTYPE" )
            {
                elementtext = reader.readElementText();
                elementint = elementtext.toUInt( &conversionok );
                if( conversionok )
                {
                    if( inmuestra )
                    {
                        if( elementint == 120 ) muestra->setMarcador( Muestra::AvisoAscenso );
                        else if( elementint == 123 ) muestra->setMarcador( Muestra::AvisoRgbm );
                        else if( elementint == 127 ) muestra->setMarcador( Muestra::ErrorTechoPso );
                        else if( elementint == 121 ) muestra->setMarcador( Muestra::ParadaSeguridadObligatoria );
                        else if( elementint == 125 ) muestra->setMarcador( Muestra::Superficie );
                        else if( elementint == 150 ) muestra->setMarcador( Muestra::Personal );
                        else if( elementint == 124 ) muestra->setMarcador( Muestra::Otro ); // empty bookmark
                        else qWarning() << "BOOKMARKTYPE unknown:" << elementint;
                    }
                    else qWarning() << "BOOKMARKTYPE outside of Muestra";
                }
                else qWarning() << "BOOKMARKTYPE is invalid:" << elementtext;
            }
            else if( reader.name() == "SACRATE" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementdouble = QLocale::c().toDouble( elementtext.replace( ',', '.' ), &conversionok );
                    if( conversionok )
                        muestra->setTasacas( elementdouble );
                    else qWarning() << "SACRATE is invalid:" << elementtext;
                }
                else
                {
                    if( generalsacrateread )
                        qWarning() << "SACRATE outside of Muestra";
                    else generalsacrateread = true;
                }
            }
            else if( reader.name() == "CYLPRESS" )
            {
                elementtext = reader.readElementText();
                if( inmuestra )
                {
                    elementint = elementtext.toUInt( &conversionok );
                    if( conversionok )
                        muestra->setPresioncilindro( elementint );
                    else qWarning() << "CYLPRESS is invalid:" << elementtext;
                }
                else qWarning() << "CYLPRESS outside of Muestra";
            }
        }
        else if( tokentype == QXmlStreamReader::EndElement )
        {
            if( reader.name() == "SAMPLE" )
            {
                if( !inmuestra ) qWarning() << "SAMPLE end while not inside a Muestra";
                inmuestra = false;
                muestras.append( muestra );
                muestra = NULL;
            }
        }
    }
    if( reader.hasError() )
        qDebug() << "QXmlStreamReader error:" << reader.errorString();
    else if( !importdate.isValid() )
        qDebug() << "DATE not found in file";
    else if( !importtime.isValid() )
        qDebug() << "TIME not found in file";
    else if( samplecnt != muestras.count() )
        qDebug() << "SAMPLECNT is" << samplecnt << "but" << muestras.count() << "found in file";
    else
    {
        quint32 muestrasoffset = 0;
        quint32 inmersionid = 0;
        QDateTime importdatetime( importdate, importtime );
        if( createnew )
        {
            idbuceador = findBuceador( buceador );
            if( idbuceador == 0 )
            {
                qWarning() << "cannot insert inmersion because buceador" << buceador->usuario() << "cannot be found";
                return;
            }
            if( idsitio == 0 )
            {
                qWarning() << "cannot insert inmersion because sitio" << sitiotext << "(" << ubicaciontext << ") is missing";
                return;
            }
            if( profundidadmaxima == 0 )
            {
                qWarning() << "cannot insert inmersion because profundidadmaxima cannot be found";
                return;
            }
            if( profundidadmedia == 0 )
            {
                qWarning() << "cannot insert inmersion because profundidadmedia cannot be found";
                return;
            }
            inmersion = new Inmersion( buceador,
                                       (buceador->inmersiones().count() + 1),
                                       importdatetime,
                                       duracion,
                                       profundidadmaxima,
                                       profundidadmedia,
                                       _sitios.value( idsitio, NULL ),
                                       intervalosuperficie,
                                       temperaturaaire,
                                       temperaturasuperficie,
                                       temperaturafondo,
                                       volumenbotella,
                                       presioninicial,
                                       presionfinal,
                                       lastre,
                                       porcentajeo2,
                                       _buddies.value( idbuddy, NULL ),
                                       NULL );
            inmersionid = insertInmersion( importdatetime,
                                           duracion,
                                           idsitio,
                                           idbuceador,
                                           intervalosuperficie,
                                           profundidadmaxima,
                                           profundidadmedia,
                                           temperaturaaire,
                                           temperaturasuperficie,
                                           temperaturafondo,
                                           volumenbotella,
                                           presioninicial,
                                           presionfinal,
                                           lastre,
                                           porcentajeo2,
                                           idbuddy,
                                           0 /* viaje is not present in xml file */ );
            buceador->addInmersion( inmersion );
            _inmersiones.insert( inmersionid, inmersion );
        }
        else
        {
            inmersionid = findInmersion( importdatetime );
            if( inmersionid == 0 )
            {
                //qDebug() << "WARNING: no inmersion found with exact datetime" << importdatetime.toString("dd-MM-yy HH:mm:ss");
                quint32 previousid = findPreviousInmersion( importdatetime );
                if( previousid == 0 )
                {
                    qWarning() << "ERROR: no inmersion with exact datetime and no previous inmersion found either, so cannot insert muestras for"
                               << importdatetime.toString("dd-MM-yy HH:mm:ss");
                    return;
                }
                Inmersion* inmersion = _inmersiones.value( previousid );
                //qDebug() << "closest previous inmersion is" << inmersion->sitio()->nombre() << ":" << inmersion->inicio().toString("dd-MM-yy HH:mm:ss");
                //qDebug() << "offset for muestras is" << inmersion->inicio().secsTo( importdatetime );
                muestrasoffset = inmersion->inicio().secsTo( importdatetime );
                inmersionid = previousid;
            }
            else inmersion = _inmersiones.value( inmersionid );
        }
        QSqlDatabase::database().transaction();
        QSqlQuery query;
        query.prepare( "INSERT INTO Muestras(IdInmersion,Tiempo,Profundidad,Presion,Temperatura,Marcador,TasaCAS,PresionCilindro) "
                       "VALUES(:idinmersion,:tiempo,:profundidad,:presion,:temperatura,:marcador,:tasacas,:presioncilindro)" );
        quint32 maxtiempo = 0;
        for( int i = 0; i < muestras.size(); ++i )
        {
            muestra = muestras.at(i);
            query.bindValue( ":idinmersion", inmersionid );
            query.bindValue( ":tiempo", muestra->tiempo() + muestrasoffset );
            query.bindValue( ":profundidad", muestra->profundidad() );
            query.bindValue( ":presion", muestra->presion() );
            query.bindValue( ":temperatura", muestra->temperatura() );
            query.bindValue( ":marcador", muestra->marcador() );
            query.bindValue( ":tasacas", muestra->tasacas() );
            query.bindValue( ":presioncilindro", muestra->presioncilindro() );
            if( !query.exec() ) qWarning() << "Error executing query:" << query.lastQuery() << " - " << query.lastError().text();
            inmersion->addMuestra( muestra->tiempo(), muestra );
            if( muestra->tiempo() > maxtiempo )
            {
                maxtiempo = muestra->tiempo();
                inmersion->setMaxTiempo( maxtiempo );
            }
        }
        /*query.prepare( "UPDATE Inmersiones SET IntervaloMuestreo=:sampleinterval WHERE Id=:idinmersion");
        query.bindValue( ":sampleinterval", sampleinterval );
        query.bindValue( ":idinmersion", inmersionid );
        if( !query.exec() ) qWarning() << "Error executing query:" << query.lastQuery() << " - " << query.lastError().text();*/
        QSqlDatabase::database().commit();
        qDebug() << inmersion->sitio()->nombre() << inmersion->muestras().count() << "muestras";
    }
}

quint32 DataStore::findInmersion( const QDateTime& inicio )
{
    Inmersion* inmersion = NULL;
    QHashIterator<quint32,Inmersion*> i( _inmersiones );
    while( i.hasNext() )
    {
        i.next();
        inmersion = i.value();
        if( inmersion->inicio() == inicio ) return i.key();
    }
    return 0;
}

quint32 DataStore::findPreviousInmersion( const QDateTime& inicio )
{
    quint32 previousid = 0;
    qint64 minimaldiff = 0;
    Inmersion* inmersion = NULL;
    QHashIterator<quint32,Inmersion*> i( _inmersiones );
    while( i.hasNext() )
    {
        i.next();
        inmersion = i.value();
        qint64 diff = inmersion->inicio().secsTo( inicio );
        if( diff > 0 && ( minimaldiff == 0 || diff < minimaldiff ) )
        {
            previousid = i.key();
            minimaldiff = diff;
        }
    }
    return previousid;
}
